<#assign StringUtils = beansWrapperFn.getStaticModels()["org.apache.commons.lang3.StringUtils"]>
WITH w_DICTIONARY_TYPE AS
(SELECT T.ID,
T.PARENT_ID,
T.CODE,
T.NAME,
T.VALUE,
T.ORDER_NUM,
T.BEGIN_DATE,
T.END_DATE ,
row_number() over(order by T_DICTIONARY_TYPE.ORDER_NUM, T_DICTIONARY_TYPE.code, t.ORDER_NUM, t.CODE) rn,
t_dictionary_type.code AS parentCode,
t_dictionary_type.name AS parentName
FROM C1_DICTIONARY_TYPE T
LEFT JOIN C1_DICTIONARY_TYPE T_DICTIONARY_TYPE
ON (Sysdate BETWEEN t_dictionary_type.begin_date AND t_dictionary_type.end_date
AND T_DICTIONARY_TYPE.ID = t.parent_id)
WHERE Sysdate BETWEEN t.begin_date AND t.end_date
<#if StringUtils.isNotBlank(parentCode)>
and (instr(','||T_DICTIONARY_TYPE.code||',', ','||:parentCode||',') > 0 or instr(','||T_DICTIONARY_TYPE.name||',', ','||:parentCode||',') > 0)
</#if>
<#if StringUtils.isNotBlank(parentId)>
and T.PARENT_ID = :parentId
</#if>
<#if StringUtils.isNotBlank(code)>
and (instr(','||T.code||',', ','||:code||',') > 0 or instr(','||T.name||',', ','||:code||',') > 0)
</#if>
)
SELECT T.*,
T.parentCode,
T.parentName
FROM w_DICTIONARY_TYPE T
WHERE t.rn BETWEEN <#if (begin_row_num??)>:begin_row_num<#else>1</#if> AND <#if (end_row_num??)>:end_row_num<#else>20</#if>

